﻿
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace Sale.Client
{
    public class NPOIHelper
    {
        public IWorkbook Workbook { get; private set; }

        public void Open(string fileName)
        {
            string ext = Path.GetExtension(fileName);
            if (ext.ToLower() == ".xls")
            {
                using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    this.Workbook = new HSSFWorkbook(fs);
                    //fs.Close();
                }
            }
            else if (ext.ToLower() == ".xlsx")
            {
                using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                {
                    this.Workbook = new XSSFWorkbook(fs);
                    //fs.Close();
                }
            }
        }

        public string GetCellValue(ICell cell)
        {
            CellType type = cell.CellType;

            string data = string.Empty;
            if (type == CellType.String)
                data = cell.StringCellValue.Trim();
            else if (type == CellType.Numeric)
            {
                if (DateUtil.IsCellDateFormatted(cell))
                    data = cell.DateCellValue.ToString();
                else
                    data = cell.NumericCellValue.ToString();
            }
            else if (type == CellType.Formula)
                data = cell.NumericCellValue.ToString();
            else if (type == CellType.Boolean)
                data = cell.BooleanCellValue.ToString();
            else
                data = string.Empty;

            return data;
        }

        public string GetCellValue(IRow row, int cellIndex)
        {
            ICell cell = row.GetCell(cellIndex);
            if (cell != null)
                return this.GetCellValue(cell);
            else
                return string.Empty;
        }
    }
}